﻿<%@ WebHandler Language="C#" Class="GZContainerMfgInfoMaint" %>

using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Linq;
using System.Collections;
using System.Collections.Generic;
using Newtonsoft.Json;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;

/**
 * Description: 工装批次管理订单处理程序
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * create Date:2022-07-12
 */

public class GZContainerMfgInfoMaint : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    uLMSCommonIBusiness commonBusiness = uMESBusinessFactory.instance.GetuLMSCommonObject();
    string ExportPath = System.Configuration.ConfigurationManager.AppSettings["ExportPath"].ToString();//导入临时路径
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "GetCurrentUserInfo":
                    GetCurrentUserInfo(context);//查询人员信息
                    break;
                case "GetContainerCurrentInfo":
                    GetContainerCurrentInfo(context);
                    break;
                case "GetMfgorder":
                    GetMfgorder(context);//查询未关联批次订单列表
                    break;
                case "SaveContinerMfgInfo":
                    SaveContinerMfgInfo(context);//添加关联
                    break;
                case "DelContinerMfgInfo":
                    DelContinerMfgInfo(context);//解除关联
                    break;
                case "ExportMfgorder"://订单导出
                    ExportMfgorder(context);
                    break;
                case "GetContainerMaterialInfo"://获取当前年份的到料批次
                    GetContainerMaterialInfo(context);
                    break;
                case "ConfirmGZPrepareManagement"://批次到料确认
                    ConfirmGZPrepareManagement(context);
                    break;
                case "CancelGZPrepareManagement"://批次到料取消
                    CancelGZPrepareManagement(context);
                    break;
                case "GetWorkcenterOfDefault"://获取工区数据
                    GetWorkcenterOfDefault(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }
    /// <summary>
    /// 获取工区信息（附带默认值）
    /// </summary>
    /// <param name="context"></param>
    public void GetWorkcenterOfDefault(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        DataTable wcdt = new DataTable();
        wcdt = commonBusiness.GetWorkCenterByFacoryID(m_UserInfo.FactoryID, false);
        DataRow[] drs = wcdt.Select(" notes is null or notes <>'已注销' ");
        if (drs.Length > 0)
        {
            wcdt = drs.CopyToDataTable();
        }
        //如果是工区长，只显示自己工区
        if (m_UserInfo.Roles.Contains("工区长"))
        {
            drs = wcdt.Select(string.Format("WORKCENTERID = '{0}'", m_UserInfo.WorkCenterID));
            if (drs.Length > 0)
            {
                wcdt = drs.CopyToDataTable();
            }
        }
        result.DBTable = wcdt;
        if (wcdt.Select(string.Format("WORKCENTERID='{0}'", m_UserInfo.WorkCenterID)).Length != 0)
        {
            result.ReturnData = m_UserInfo.WorkCenterID;//默认当前人的工区ID
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 获取当前年份的到料批次
    /// </summary>
    /// <param name="context"></param>
    public void GetContainerMaterialInfo(HttpContext context)
    {
        Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["param"].ToString());
        string sql = string.Format(@"SELECT Distinct NVL(g.isallset,'是') isallset,c.containername,c.productname,c.workflowstepname,c.qty,
                        c.containerid,cc.originalstartdate,gz.startdate,gz.enddate,gz.ordertype,gz.plantype,gz.priority,gz.risklevel,
                        g.createdate,g.lastchangedate,e.fullname createname,ee.fullname changename
                        FROM containercurrentinfo c 
                        LEFT JOIN container cc ON cc.containerid = c.containerid
                        LEFT JOIN product p ON p.productid = c.productid
                        LEFT JOIN gzpreparemanagement g ON g.containerid = c.containerid
                        LEFT JOIN bomproductsubproduct bp ON bp.subproductname = c.productname AND bp.subproductrev = c.productrevision
                        LEFT JOIN gzordertypemanagement gz ON gz.containername = (bp.productname || REPLACE(c.containername,c.productname,''))
                        LEFT JOIN employee e ON e.employeeid = g.createemployeeid
                        LEFT JOIN employee ee ON ee.employeeid = g.lastchangeemployeeid
                        WHERE c.containerstatus = 1 AND c.originalfactoryid = '{0}' AND to_char(c.originalstartdate,'YYYY') = '{1}'
                        AND p.istopgz IS NULL ", m_UserInfo.FactoryID, DateTime.Now.ToString("yyyy"));
        //if (m_UserInfo.Roles.Contains("工区长"))
        //{
        //    sql += string.Format(" AND c.workcenterid = '{0}' ", m_UserInfo.WorkCenterID);
        //}
        //查询条件
        if (!string.IsNullOrWhiteSpace(dic["productName"]))
        {
            sql += string.Format(" AND c.productname like '{0}%'", dic["productName"]);
        }
        if (!string.IsNullOrWhiteSpace(dic["containerName"]))
        {
            sql += string.Format(" AND c.containerName like '{0}%'", dic["containerName"]);
        }
        if (!string.IsNullOrWhiteSpace(dic["workCenter"]))
        {
            sql += string.Format(" AND c.workcenterid = '{0}'", dic["workCenter"]);
        }
        if (!string.IsNullOrWhiteSpace(dic["isallset"]))
        {
            sql += dic["isallset"] == "0" ? " AND g.isallset = '否'" : " AND NVL(g.isallset,'是') = '是'";
        }
        if (!string.IsNullOrWhiteSpace(dic["startContainerTime"]))
        {
            sql += string.Format(" AND to_char(cc.originalstartdate,'YYYY-MM-DD') = '{0}'", dic["startContainerTime"]);
        }
        if (!string.IsNullOrWhiteSpace(dic["orderType"]))
        {
            sql += string.Format(" AND gz.ordertype = '{0}'", dic["orderType"]);
        }
        if (!string.IsNullOrWhiteSpace(dic["planType"]))
        {
            sql += string.Format(" AND gz.plantype = '{0}'", dic["planType"]);
        }
        if (!string.IsNullOrWhiteSpace(dic["riskLevel"]))
        {
            sql += string.Format(" AND gz.risklevel = '{0}'", dic["riskLevel"]);
        }
        if (!string.IsNullOrWhiteSpace(dic["priority"]))
        {
            sql += string.Format(" AND gz.priority = '{0}'", dic["priority"]);
        }
        if (!string.IsNullOrWhiteSpace(dic["instore"]))
        {
            sql += dic["instore"] == "1" ? " AND c.isinstore = 1" : " AND c.isinstore != 1";
        }
        uMESPagingDataDTO paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(context.Request.Form["pageIndex"].ToString());//页码
        paraPage.PageSize = Convert.ToInt32(context.Request.Form["PageSize"].ToString());//每页页数
        paraPage.strSQL = sql;
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 批次到料确认
    /// </summary>
    /// <param name="context"></param>
    public void ConfirmGZPrepareManagement(HttpContext context)
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["param"].ToString());
        if (dt.Rows.Count == 0)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "请选择数据" }));
            return;
        }
        ArrayList list = new ArrayList();
        string updateSql = "UPDATE GZPREPAREMANAGEMENT g SET g.isallset = '是',g.lastchangeemployeeid='{0}',g.lastchangedate=sysdate WHERE g.containerid = '{1}'";
        foreach (DataRow item in dt.Rows)
        {
            list.Add(string.Format(updateSql, m_UserInfo.EmployeeID, item["containerid"].ToString()));
        }
        DatabaseHelper.ExecuteSqlTran(list);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "保存成功" }));
    }

    /// <summary>
    /// 批次到料取消
    /// </summary>
    /// <param name="context"></param>
    public void CancelGZPrepareManagement(HttpContext context)
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["param"].ToString());
        if (dt.Rows.Count == 0)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "请选择数据" }));
            return;
        }
        ArrayList list = new ArrayList();
        string sql = "select 1 from GZPREPAREMANAGEMENT g where g.containerid = '{0}'";
        string insertSql = "INSERT INTO GZPREPAREMANAGEMENT g(ID,CONTAINERID,CONTAINERNAME,ISALLSET,CREATEEMPLOYEEID,CREATEDATE) VALUES('{0}','{1}','{2}','{3}','{4}',sysdate)";
        string updateSql = "UPDATE GZPREPAREMANAGEMENT g SET g.isallset = '否',g.lastchangeemployeeid='{0}',g.lastchangedate=sysdate WHERE g.containerid = '{1}'";
        foreach (DataRow item in dt.Rows)
        {
            if (DatabaseHelper.GetSingle(string.Format(sql, item["containerid"].ToString())) == null)
            {
                list.Add(string.Format(insertSql, Guid.NewGuid().ToString(), item["containerid"].ToString(), item["containername"].ToString(), "否", m_UserInfo.EmployeeID));
            }
            else
            {
                list.Add(string.Format(updateSql, m_UserInfo.EmployeeID, item["containerid"].ToString()));
            }
            //list.Add(string.Format(updateSql, m_UserInfo.EmployeeID, item["id"].ToString()));
        }
        DatabaseHelper.ExecuteSqlTran(list);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "保存成功" }));
    }

    //获取当前登录用户信息
    void GetCurrentUserInfo(HttpContext context)
    {
        if (m_UserInfo != null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "", ReturnData = m_UserInfo }));
        }
    }

    // 查询批次信息
    void GetContainerCurrentInfo(HttpContext context)
    {
        string sql = @"
SELECT c.Containername,c.ypcontainertype AS yptype, c.Qty,cci.Productname,cci.productdescription Description, cci.productrevision, c.Factorystartdate, 
        NVL(p.bomid,'') AS bomid, NVL(p.bombaseid,'') as bombaseid ,c.Containerid, e.Fullname,  
        cdo.Cdoname,decode(mt.materialtypename,'管材',0,c.isselfmfg) isselfmfg, cci.fromworkcentername WORKCENTERNAME,cci.FactoryName,                      
        cci.mfgordername,P.PRODUCTID,DECODE(CDO.CDONAME,'Workflow',c.mfgworkflowid,w.workflowid) workflowid,swc.workflowid faworkflowid,
        (NVL(cci.workflowroute,cci.productname)||'/'||REPLACE(cci.containername,cci.productname||'/','')) containerBatch,
        DECODE(pt.producttypename,'子工艺虚拟件'，'子卡','主卡') MainOrSonContainer,
        p.firstusedproduct, c.containercomments,p.istopgz,p.workflowroute,CASE WHEN cci.mfgordername IS NULL THEN '未关联' ELSE '已关联' END hasmfgorder
FROM Container c                         
LEFT JOIN containercurrentinfo cci ON cci.containerid=c.containerid                         
INNER JOIN Product p on p.Productid = c.Productid  
LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid                                             
INNER JOIN Workflow w ON w.Workflowid = cci.Workflowid                       
INNER JOIN Cdodefinition cdo ON cdo.Cdodefid = w.Cdotypeid                     
LEFT  JOIN Factory fa ON  fa.Factoryid =cci.originalfactoryid                   
INNER JOIN Employee e ON e.Employeeid = c.startemployeeid                      
LEFT JOIN materialtype mt ON mt.materialtypeid=p.materialtypeid                 
LEFT JOIN (
  SELECT swc.containerid,swc.subworkflowid,max(swc.createdate) MAXcreatedate FROM subworkflowcheckinfo swc
  GROUP BY swc.containerid,swc.subworkflowid
) a ON a.containerid=cci.containerid AND a.subworkflowid=cci.workflowid 
LEFT JOIN SUBWORKFLOWCHECKINFO SWC ON SWC.CONTAINERID=CCI.CONTAINERID AND SWC.SUBWORKFLOWID=CCI.WORKFLOWID AND a.MAXcreatedate=swc.createdate
WHERE c.Parentcontainerid IS NULL AND c.Status = 1 AND c.Qty >= 0 AND c.Containerid != c.Containername AND CCI.WorkflowStepName!='工艺确认'
";
        if (context.Request["mfgordername"] != null && context.Request["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername LIKE '%{0}%'", context.Request["mfgordername"].ToString());
        }
        if (context.Request["containername"] != null && context.Request["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername LIKE '%{0}%'", context.Request["containername"].ToString());
        }
        if (context.Request["productname"] != null && context.Request["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname LIKE '%{0}%'", context.Request["productname"].ToString());
        }
        //主制分厂
        if (context.Request["factoryid"] != null && context.Request["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", context.Request["factoryid"].ToString());
        }
        //主制工区
        if (context.Request["workcenterid"] != null && context.Request["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", context.Request["workcenterid"].ToString());
        }
        //开卡人
        if (context.Request["starter"] != null && context.Request["starter"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname = '{0}'", context.Request["starter"].ToString());
        }
        //开卡时间
        if (context.Request["startdateBegin"] != null && context.Request["startdateBegin"].ToString() != "")
        {
            sql += string.Format(" AND c.originalstartdate >= to_date('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", context.Request["startdateBegin"].ToString());
        }
        if (context.Request["startdateEnd"] != null && context.Request["startdateEnd"].ToString() != "")
        {
            sql += string.Format(" AND c.originalstartdate <= to_date('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", context.Request["startdateEnd"].ToString());
        }
        //状态
        if (context.Request["status"] != null)
        {
            if (context.Request["status"].ToString() == "已关联")
            {
                sql += " AND cci.mfgordername IS NOT NULL ";
            }
            if (context.Request["status"].ToString() == "未关联")
            {
                sql += " AND cci.mfgordername IS NULL ";
            }
        }

        sql += @"
GROUP BY c.Containername,c.ypcontainertype, c.Qty,cci.Productname,cci.productdescription,cci.Productrevision, 
         c.Factorystartdate,p.bomid,p.bombaseid,c.Containerid, e.Fullname,cdo.Cdoname,mt.materialtypename,c.isselfmfg,
         cci.fromworkcentername,cci.FactoryName,cci.Mfgordername,P.PRODUCTID,c.mfgworkflowid,w.workflowid,swc.workflowid,
         cci.workflowroute,cci.containername,pt.producttypename,p.firstusedproduct, c.containercomments,p.istopgz,p.workflowroute
ORDER BY cci.Mfgordername,c.CONTAINERNAME
";
        uMESPagingDataDTO paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(context.Request.Form["pageNumber"].ToString());//页码
        paraPage.PageSize = Convert.ToInt32(context.Request.Form["PageSize"].ToString());//每页页数
        paraPage.strSQL = sql;
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        foreach (DataColumn col in result.DBTable.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    // 查询订单
    void GetMfgorder(HttpContext context)
    {
        string sql = @"
SELECT mfg.mfgorderid,mfg.mfgordername,od.ordertypename,f.factoryname,f.factoryid,mfg.qty,pb.productname,p.description,p.productid,p.productrevision
FROM mfgorder mfg
LEFT JOIN productbase pb1 ON pb1.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,mfg.productid)
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
LEFT JOIN factory f ON f.factoryid = mfg.factoryid
LEFT JOIN ordertype od ON od.ordertypeid = mfg.ordertypeid
LEFT JOIN containercurrentinfo cci ON cci.mfgorderid = mfg.mfgorderid
WHERE cci.containerid is null
";
        if (context.Request["productname"] != null && context.Request["productname"].ToString() != "")
        {
            sql += string.Format(" AND pb.productname LIKE '%{0}%'", context.Request["productname"].ToString());
        }

        //主制分厂
        if (context.Request["factoryid"] != null && context.Request["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", context.Request["factoryid"].ToString());
        }

        uMESPagingDataDTO paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(context.Request.Form["pageNumber"].ToString());//页码
        paraPage.PageSize = Convert.ToInt32(context.Request.Form["PageSize"].ToString());//每页页数
        paraPage.strSQL = sql;
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        foreach (DataColumn col in result.DBTable.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    //添加关联
    void SaveContinerMfgInfo(HttpContext context)
    {
        var containerid = context.Request.Form["containerid"].ToString();
        var mfgorderid = context.Request.Form["mfgorderid"].ToString();
        var mfgordername = context.Request.Form["mfgordername"].ToString();
        var planQty = int.Parse(context.Request.Form["planQty"].ToString());
        string sql = "UPDATE containercurrentinfo cci SET cci.mfgorderid='{0}',cci.mfgordername='{1}',planqty={2} WHERE cci.containerid='{3}'";
        sql = string.Format(sql, mfgorderid, mfgordername, planQty, containerid);
        int updateContainerCurrentInfo = DatabaseHelper.ExecuteSql(sql);
        sql = "UPDATE container c SET c.mfgorderid='{0}' WHERE c.containerid='{1}'";
        sql = string.Format(sql, mfgorderid, containerid);
        int updateContainer = DatabaseHelper.ExecuteSql(sql);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "", DBTable = null }));
    }

    //解除关联
    void DelContinerMfgInfo(HttpContext context)
    {
        var containerid = context.Request.Form["containerid"].ToString();
        string sql = @" SELECT csd.containerid FROM containerstepdispatch csd WHERE csd.isuse = 0 AND  csd.containerid = '{0}'";
        sql = string.Format(sql, containerid);

        if (DatabaseHelper.Query_ReturnDatTable(sql).Rows.Count == 0)
        {
            sql = "UPDATE containercurrentinfo cci SET cci.mfgorderid = null,cci.mfgordername = null WHERE cci.containerid='{0}'";
            sql = string.Format(sql, containerid);
            int updateContainerCurrentInfo = DatabaseHelper.ExecuteSql(sql);
            sql = "UPDATE container c SET c.mfgorderid = null WHERE c.containerid='{0}'";
            sql = string.Format(sql, containerid);
            int updateContainer = DatabaseHelper.ExecuteSql(sql);
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "", DBTable = null }));
        }
        else
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "批次已产生派工数据，不能解除关联！", DBTable = null }));
        }
    }


    private void ExportMfgorder(HttpContext context)
    {
        var strPath = context.Request.MapPath(context.Request.ApplicationPath);
        var exDt = GetContainerCurrentInfoData(context);//new DataTable();
        var url = DataTableExportToExcel(exDt, strPath, context);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "", ReturnData = url }));
    }
    private DataTable GetContainerCurrentInfoData(HttpContext context)
    {
        string sql = @"
SELECT c.Containername,c.ypcontainertype AS yptype, c.Qty,cci.Productname,cci.productdescription Description, cci.productrevision, c.Factorystartdate, 
        NVL(p.bomid,'') AS bomid, NVL(p.bombaseid,'') as bombaseid ,c.Containerid, e.Fullname,  
        cdo.Cdoname,decode(mt.materialtypename,'管材',0,c.isselfmfg) isselfmfg, cci.fromworkcentername WORKCENTERNAME,cci.FactoryName,                      
        cci.mfgordername,P.PRODUCTID,DECODE(CDO.CDONAME,'Workflow',c.mfgworkflowid,w.workflowid) workflowid,swc.workflowid faworkflowid,
        (NVL(cci.workflowroute,cci.productname)||'/'||REPLACE(cci.containername,cci.productname||'/','')) containerBatch,
        DECODE(pt.producttypename,'子工艺虚拟件'，'子卡','主卡') MainOrSonContainer,
        p.firstusedproduct, c.containercomments,p.istopgz,p.workflowroute,CASE WHEN cci.mfgordername IS NULL THEN '未关联' ELSE '已关联' END hasmfgorder
FROM Container c                         
LEFT JOIN containercurrentinfo cci ON cci.containerid=c.containerid                         
INNER JOIN Product p on p.Productid = c.Productid  
LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid                                             
INNER JOIN Workflow w ON w.Workflowid = cci.Workflowid                       
INNER JOIN Cdodefinition cdo ON cdo.Cdodefid = w.Cdotypeid                     
LEFT  JOIN Factory fa ON  fa.Factoryid =cci.originalfactoryid                   
INNER JOIN Employee e ON e.Employeeid = c.startemployeeid                      
LEFT JOIN materialtype mt ON mt.materialtypeid=p.materialtypeid                 
LEFT JOIN (
  SELECT swc.containerid,swc.subworkflowid,max(swc.createdate) MAXcreatedate FROM subworkflowcheckinfo swc
  GROUP BY swc.containerid,swc.subworkflowid
) a ON a.containerid=cci.containerid AND a.subworkflowid=cci.workflowid 
LEFT JOIN SUBWORKFLOWCHECKINFO SWC ON SWC.CONTAINERID=CCI.CONTAINERID AND SWC.SUBWORKFLOWID=CCI.WORKFLOWID AND a.MAXcreatedate=swc.createdate
WHERE c.Parentcontainerid IS NULL AND c.Status = 1 AND c.Qty >= 0 AND c.Containerid != c.Containername AND CCI.WorkflowStepName!='工艺确认'
";
        if (context.Request["mfgordername"] != null && context.Request["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername LIKE '%{0}%'", context.Request["mfgordername"].ToString());
        }
        if (context.Request["containername"] != null && context.Request["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername LIKE '%{0}%'", context.Request["containername"].ToString());
        }
        if (context.Request["productname"] != null && context.Request["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname LIKE '%{0}%'", context.Request["productname"].ToString());
        }
        //主制分厂
        if (context.Request["factoryid"] != null && context.Request["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", context.Request["factoryid"].ToString());
        }
        //主制工区
        if (context.Request["workcenterid"] != null && context.Request["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", context.Request["workcenterid"].ToString());
        }
        //开卡人
        if (context.Request["starter"] != null && context.Request["starter"].ToString() != "")
        {
            sql += string.Format(" AND c.startemployeeid = '{0}'", context.Request["starter"].ToString());
        }
        //开卡时间
        if (context.Request["startdateBegin"] != null && context.Request["startdateBegin"].ToString() != "")
        {
            sql += string.Format(" AND c.originalstartdate >= to_date('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", context.Request["startdateBegin"].ToString());
        }
        if (context.Request["startdateEnd"] != null && context.Request["startdateEnd"].ToString() != "")
        {
            sql += string.Format(" AND c.originalstartdate <= to_date('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", context.Request["startdateEnd"].ToString());
        }
        //状态
        if (context.Request["status"] != null)
        {
            if (context.Request["status"].ToString() == "已关联")
            {
                sql += " AND cci.mfgordername IS NOT NULL ";
            }
            if (context.Request["status"].ToString() == "未关联")
            {
                sql += " AND cci.mfgordername IS NULL ";
            }
        }

        sql += @"
GROUP BY c.Containername,c.ypcontainertype, c.Qty,cci.Productname,cci.productdescription,cci.Productrevision, 
         c.Factorystartdate,p.bomid,p.bombaseid,c.Containerid, e.Fullname,cdo.Cdoname,mt.materialtypename,c.isselfmfg,
         cci.fromworkcentername,cci.FactoryName,cci.Mfgordername,P.PRODUCTID,c.mfgworkflowid,w.workflowid,swc.workflowid,
         cci.workflowroute,cci.containername,pt.producttypename,p.firstusedproduct, c.containercomments,p.istopgz,p.workflowroute
ORDER BY cci.Mfgordername,c.CONTAINERNAME
";
        return DatabaseHelper.Query_ReturnDatTable(sql);
    }

    private string DataTableExportToExcel(DataTable SourceTable, string baseUrl, HttpContext context)
    {
        var coloumsCount = SourceTable.Columns.Count;
        var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
        var ms = new System.IO.MemoryStream();
        // 单元格样式设置
        var style = workbook.CreateCellStyle();
        //水平对齐居中
        style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
        //设置字体
        var Font = workbook.CreateFont();
        Font.FontName = "微软雅黑";
        style.SetFont(Font);
        //'设置边框
        style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        var headerCellStyle = workbook.CreateCellStyle();
        //居中显示
        headerCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
        //设置边框
        headerCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        headerCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        headerCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        headerCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        //设置背景色
        headerCellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
        headerCellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
        //加粗
        var headerFont = workbook.CreateFont();
        //headerFont.Boldweight = Short.MaxValue;
        headerFont.FontName = "微软雅黑";
        headerCellStyle.SetFont(headerFont);
        //创建sheet
        var sheet = workbook.CreateSheet("sheet1");
        var row = sheet.CreateRow(0);
        row.CreateCell(0).SetCellValue("生产订单");
        row.GetCell(0).CellStyle = headerCellStyle;
        row.CreateCell(1).SetCellValue("管理状态");
        row.GetCell(1).CellStyle = headerCellStyle;
        row.CreateCell(2).SetCellValue("批次号");
        row.GetCell(2).CellStyle = headerCellStyle;
        row.CreateCell(3).SetCellValue("创建时间");
        row.GetCell(3).CellStyle = headerCellStyle;
        row.CreateCell(4).SetCellValue("零件名称");
        row.GetCell(4).CellStyle = headerCellStyle;
        row.CreateCell(5).SetCellValue("数量");
        row.GetCell(5).CellStyle = headerCellStyle;
        row.CreateCell(6).SetCellValue("开卡人");
        row.GetCell(6).CellStyle = headerCellStyle;
        row.CreateCell(7).SetCellValue("工区");
        row.GetCell(7).CellStyle = headerCellStyle;
        row.CreateCell(8).SetCellValue("分厂");
        row.GetCell(8).CellStyle = headerCellStyle;
        row.CreateCell(9).SetCellValue("主子卡");
        row.GetCell(9).CellStyle = headerCellStyle;
        for (var index = 0; index < SourceTable.Rows.Count - 1; index++)
        {
            row = sheet.CreateRow(index + 1);
            row.CreateCell(0).SetCellValue(SourceTable.Rows[index]["mfgordername"].ToString());
            row.CreateCell(1).SetCellValue(SourceTable.Rows[index]["hasmfgorder"].ToString());
            row.CreateCell(2).SetCellValue(SourceTable.Rows[index]["containername"].ToString());
            row.CreateCell(3).SetCellValue(SourceTable.Rows[index]["factorystartdate"].ToString());
            row.CreateCell(4).SetCellValue(SourceTable.Rows[index]["qty"].ToString());
            row.CreateCell(6).SetCellValue(SourceTable.Rows[index]["fullname"].ToString());
            row.CreateCell(7).SetCellValue(SourceTable.Rows[index]["workcentername"].ToString());
            row.CreateCell(8).SetCellValue(SourceTable.Rows[index]["factoryname"].ToString());
            row.CreateCell(9).SetCellValue(SourceTable.Rows[index]["mainorsoncontainer"].ToString());
        }
        sheet.SetColumnWidth(0, 20 * 256);
        sheet.SetColumnWidth(1, 20 * 256);
        sheet.SetColumnWidth(2, 20 * 256);
        sheet.SetColumnWidth(3, 20 * 256);
        sheet.SetColumnWidth(4, 15 * 256);
        sheet.SetColumnWidth(5, 30 * 256);
        sheet.SetColumnWidth(6, 10 * 256);
        sheet.SetColumnWidth(7, 30 * 256);
        sheet.SetColumnWidth(8, 30 * 256);
        sheet.SetColumnWidth(9, 30 * 256);
        workbook.Write(ms);
        ms.Flush();
        ms.Position = 0;
        var fileName = ExportPath.Replace("~", "") + "工装工具订单导出" + DateTime.Now.ToString("yyyyMMddHHmmddssffff") + ".xls";
        var strPath = baseUrl + fileName;
        if (!System.IO.File.Exists(strPath))
        {
            var fs = new System.IO.FileStream(strPath, System.IO.FileMode.Create);

            var Buffer = ms.ToArray();
            fs.Write(Buffer, 0, Buffer.Length);
            fs.Flush();
            fs.Close();
            ms.Close();
            ms.Dispose();
            var file = new System.IO.FileInfo(strPath);
        }
        return fileName;
    }
}